package beans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class HeatMapSearchBean {

	
	public HMConfig search(String city, String state, String activity) {
		
		HMConfig config = null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		    Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/nstweb?user=root&password=root");
		    Statement s = c.createStatement();	
			
			String query = "SELECT * FROM overlay WHERE city='"+city+"' AND state='"+state+"' AND activity='"+activity+"'";
			System.out.println(query);
			ResultSet rs = s.executeQuery(query);
			
			while(rs.next()) {
				
				int[] nusers = new int[4];
				int[] ntracks = new int[4];
				
				for(int i=0; i<4;i++) {
					nusers[i] = rs.getInt("nuser"+(i+1)); // db count from 1 to 4
					ntracks[i] = rs.getInt("ntrack"+(i+1));
				}
				
				int population = rs.getInt("population");
				
				double no = rs.getDouble("north");
				double so = rs.getDouble("south");
				double ea = rs.getDouble("east");
				double we = rs.getDouble("west");
				
				if(no == 0 && so == 0 && ea == 0 && we == 0)
					return null;
				
				String r_city = rs.getString("city");
				String r_state = rs.getString("state");
				String r_activity = rs.getString("activity");
				
				config = new HMConfig(r_city,r_state,r_activity,nusers,ntracks,population,no,so,ea,we);
			}	
			
			s.close();
			c.close();

			
		} catch(Exception e) {
			e.printStackTrace();
		}
		
		return config;
	}
	
	public static void main(String[] args) {
		HeatMapSearchBean hmsb = new HeatMapSearchBean();
		HMConfig config = hmsb.search("Pyongyang", "KP", "running");
		System.out.println(config);
	}
}
